﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ZX.BLL.Extend;
using ZX.CRM.Library;
using System.Data;
using System.Data.SqlClient;
using ZX.SPL.ORM;
using ZX.DBModule;
using ZX.Common;
using ZX.Common.Extend;
using ZX.DAL.DBExtend;
using ZX.BLL;

namespace ZX.BPM.Library
{
    public class BHF_TaskInstanceView : BaseBussnies
    {

        /// <summary>
        /// 获取待办任务
        /// </summary>
        /// <param name="WorkflowID"></param>
        /// <param name="WorkflowInfo"></param>
        /// <param name="UserID"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        public IList<BaseEntity> GetTaskInstanceList(string WorkflowID, string WorkflowInfo, string UserID, int PageIndex, int PageSize)
        {

            //@PageIndex				--页索引
            //@PageSize				--页大小
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@WorkflowID",SqlDbType.VarChar,100),
                new SqlParameter("@WorkflowInfo",SqlDbType.VarChar,200),
                new SqlParameter("@UserID",SqlDbType.VarChar,100),
                new SqlParameter("@PageIndex",SqlDbType.Int,1),
                new SqlParameter("@PageSize",SqlDbType.Int,1)
             };

            IList<BaseEntity> results = new List<BaseEntity>();
            sqlparams[0].Value = WorkflowID;
            sqlparams[1].Value = WorkflowInfo;
            sqlparams[2].Value = UserID;
            sqlparams[3].Value = PageIndex;
            sqlparams[4].Value = PageSize;
            DataSet ds = this.RunProcedure("sp_GetWaitingTaskList", sqlparams);
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                ZX.SPL.ORM.ObjectMapper om = new SPL.ORM.ObjectMapper(new V_HF_TaskInstanceView());

                results = om.DatatableToObject(dt);

            }

            return results;
        }

        /// <summary>
        /// 获取待办任务
        /// </summary>
        /// <param name="WorkflowID"></param>
        /// <param name="WorkflowInfo"></param>
        /// <param name="UserID"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        public DataTable GetTaskDatatable(string WorkflowID, string WorkflowInfo, string UserID, int PageIndex, int PageSize)
        {
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@WorkflowID",SqlDbType.VarChar,100),
                new SqlParameter("@WorkflowInfo",SqlDbType.VarChar,200),
                new SqlParameter("@UserID",SqlDbType.VarChar,100),
                new SqlParameter("@PageIndex",SqlDbType.Int,1),
                new SqlParameter("@PageSize",SqlDbType.Int,1)
             };

            IList<BaseEntity> results = new List<BaseEntity>();
            sqlparams[0].Value = WorkflowID;
            sqlparams[1].Value = WorkflowInfo;
            sqlparams[2].Value = UserID;
            sqlparams[3].Value = PageIndex;
            sqlparams[4].Value = PageSize;
            DataSet ds = this.RunProcedure("sp_GetWaitingTaskList", sqlparams);
            DataTable boundTable = null;
            ///******************todo zhuyun********************/
            //if (!string.IsNullOrEmpty(selectname))
            //{
            //    DataTable orders = ds.Tables[0];
            //    var query =
            //        from order in orders.AsEnumerable()
            //        where order.Field<string>("FlowInstanceCaption").Contains(selectname)
            //        select order;
            //    boundTable = query.CopyToDataTable<DataRow>();
            //}
            ///******************todo zhuyun********************/
            if (ds != null && ds.Tables.Count > 0)
            {
                if (boundTable != null)
                {
                    return boundTable;
                }
                else
                {
                    return ds.Tables[0];
                }
            }
            else
                return null;
        }

        /// <summary>
        /// 获取待办任务数
        /// </summary>
        /// <param name="WorkflowID"></param>
        /// <param name="WorkflowInfo"></param>
        /// <param name="UserID"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        public int GetTaskInstanceCount(string WorkflowID, string WorkflowInfo, string UserID, int PageIndex, int PageSize)
        {

            //@WorkflowID				--流程模版ID
            //@WorkflowInfo			--流程信息(编号或标题)
            //@UserID					--用户ID 
            //@PageIndex				--页索引
            //@PageSize				--页大小
            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@WorkflowID",SqlDbType.VarChar,100),
                new SqlParameter("@WorkflowInfo",SqlDbType.VarChar,200),
                new SqlParameter("@UserID",SqlDbType.VarChar,100),
                new SqlParameter("@PageIndex",SqlDbType.Int,1),
                new SqlParameter("@PageSize",SqlDbType.Int,1)
             };
            int count = 0;
            sqlparams[0].Value = WorkflowID;
            sqlparams[1].Value = WorkflowInfo;
            sqlparams[2].Value = UserID;
            sqlparams[3].Value = PageIndex;
            sqlparams[4].Value = PageSize;
            DataSet ds = this.RunProcedure("sp_GetWaitingTaskCount", sqlparams);
            if (ds != null && ds.Tables.Count > 0)
            {
                //TaskList = ZX.Common.Globals.JSonToObject<IList<V_HF_TaskInstanceView>>(blist.ToJSON());
                if (ds.Tables[0].Rows.Count > 0)
                {
                    count = (ds.Tables[0].Rows[0]["TotalCount"] == null) ? 0 : Convert.ToInt32(ds.Tables[0].Rows[0]["TotalCount"]);
                }
            }

            return count;
            //IList<V_HF_TaskInstanceView>
        }
        /// <summary>
        /// 获取待办任务
        /// </summary>
        /// <param name="WorkflowID"></param>
        /// <param name="WorkflowInfo"></param>
        /// <param name="UserID"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        public DataSet GetTaskInstanceClassList(string UserID)
        {
            //@UserID	--用户ID 
            SqlParameter[] sqlparams = new SqlParameter[]{
              new SqlParameter("@UserID",SqlDbType.VarChar,100),
             
             };
            sqlparams[0].Value = UserID;
            DataSet ds = this.RunProcedure("sp_GetWaitingTaskTotal", sqlparams);
            return ds;
        }


        /// <summary>
        /// 获取可以发起的流程
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public DataTable GetWFList(string UserID)
        {
            SqlParameter[] sqlparams = new SqlParameter[]{
              new SqlParameter("@UserID",SqlDbType.VarChar,100),
             
             };
            sqlparams[0].Value = UserID;
            DataSet ds = this.RunProcedure("HF_GetAllowsStartWFList", sqlparams);
            return ds.Tables[0];
        }

        /// <summary>
        /// 消息列表
        /// </summary>
        /// <param name="UserID">string UserID, string param</param>
        /// <returns></returns>
        public DataTable GetMSGList(IDictionary<string, object> dictionList)
        {

            #region Sql查询脚本
            string listInitSql = "select {0} {1} from DatSysNews d left join DatSysNewsInfo i on d.ID = i.NID where 1=1 and d.ISDelete=0 ";
            string listColumnSql = "d.createPeopleName,d.title,d.content,createDate=CONVERT(varchar(10), isnull(d.createDate,''1900-01-01''), 121),d.sendMode,d.ID,i.NID,i.recevePeopleID,i.readDate, (case when isnull(i.readDate,'''')='''' then ''[未读]'' when isnull(i.readDate,'''')<>'''' then ''[已读]'' end) as wdyd, AddTime=CONVERT(varchar(16), isnull(d.createDate, ''1900-01-01''), 121) ";

            string listSortSql = " order by d.createDate desc";

            StringBuilder sbCommandText = new StringBuilder();
            sbCommandText.Append("set nocount on\n");
            sbCommandText.Append("declare @ExecSql varchar(4000)\n");
            sbCommandText.Append("declare @SqlCondition varchar(1000)\n");
            sbCommandText.Append("declare @PageIndex int \n");
            sbCommandText.Append("declare @PageSize int \n");
            sbCommandText.Append("declare @IsCount tinyint \n");
            sbCommandText.Append("set @SqlCondition = ''  --初始化\n");
            #region 初始化分页参数
            sbCommandText.AppendFormat("set @PageIndex={0} \n", (dictionList.ContainsKey("pageIndex")) ? Convert.ToInt32(dictionList["pageIndex"]) : 1);
            sbCommandText.AppendFormat("set @PageSize={0} \n", (dictionList.ContainsKey("pageSize")) ? Convert.ToInt32(dictionList["pageSize"]) : 1);
            sbCommandText.AppendFormat("set @IsCount='{0}' \n", (dictionList.ContainsKey("isCount")) ? dictionList["isCount"].ToString() : "");
            #endregion

            if (dictionList.ContainsKey("LoginName"))
            {
                sbCommandText.AppendFormat("set @SqlCondition=@SqlCondition+ ' and  i.recevePeopleID=(select UserID From [DatUser] where LoginName=''{0}'')' \n", dictionList["LoginName"].ToString());
            }

            if (dictionList.ContainsKey("WhereParams"))
            {
                sbCommandText.AppendFormat("set @SqlCondition=@SqlCondition+ ' {0}' \n", dictionList["WhereParams"].ToString());
            }


            sbCommandText.Append("if(@IsCount = '1')\n");
            sbCommandText.Append("  begin\n");
            sbCommandText.Append("      set @ExecSql = '" + string.Format(listInitSql, "", "count(*) as cx") + "' + @SqlCondition\n");
            sbCommandText.Append("  end\n");
            sbCommandText.Append("else\n");
            sbCommandText.Append("  begin\n");
            sbCommandText.Append("      set @ExecSql = '" + string.Format(listInitSql, "top ' + str(@PageSize) + '", listColumnSql) + "'\n");
            sbCommandText.Append("      if(@PageIndex = 1)\n");
            sbCommandText.Append("          begin\n");
            sbCommandText.Append("              set @ExecSql = @ExecSql + @SqlCondition + '" + listSortSql + "'\n");
            sbCommandText.Append("          end\n");
            sbCommandText.Append("      else\n");
            sbCommandText.Append("          begin\n");
            sbCommandText.Append("              set @ExecSql = @ExecSql + ' and d.id not in (" + string.Format(listInitSql, "top ' + str((@PageIndex - 1) * @PageSize) + '", "d.id") + "' + @SqlCondition + '" + listSortSql + ")' + @SqlCondition + '" + listSortSql + "'\n");
            sbCommandText.Append("          end\n");
            sbCommandText.Append("  end\n");
            sbCommandText.Append("exec(@ExecSql)\n");

            #endregion Sql查询脚本
            #region 执行Sql查询脚本
            return new DAL.DBExtend.DbSql().ExecuteQuery(sbCommandText.ToString());

            #endregion



            //            StringBuilder SqlCommonBuilder = new StringBuilder();
            //            string SqlCommandText = @"select {0} {1} from DatSysNews d left join DatSysNewsInfo i
            //on d.ID = i.NID where i.recevePeopleID=(select UserID From [DatUser] where LoginName='{2}') and i.IsDelete =0  {3} order by d.createDate desc ";
            //            string SqlFullCountList = "top 100";
            //            string SqlColumnsName = "d.createPeopleName,d.title,d.content,createDate=CONVERT(varchar(10), isnull(d.createDate,'1900-01-01'), 121),d.sendMode,i.ID,i.NID,i.recevePeopleID,i.readDate ";

            //            SqlCommonBuilder.AppendFormat(SqlCommandText, SqlFullCountList, SqlColumnsName, UserID, param);


            //            return new DAL.DBExtend.DbSql().ExecuteQuery(SqlCommonBuilder.ToString());
        }

        /// <summary>
        /// 我的消息反馈列表
        /// </summary>
        /// <param name="UserID">string UserID, string param</param>
        /// <returns></returns>
        public DataTable GetMSGReply(IDictionary<string, object> dictionList)
        {

            #region Sql查询脚本
            string listInitSql = @"select {0} {1} From [DatSysNewsMonth] as x where 1=1 and x.ISDelete=0 ";
            string listColumnSql = " *";

            string listSortSql = " order by x.createDate desc";

            StringBuilder sbCommandText = new StringBuilder();
            sbCommandText.Append("set nocount on\n");
            sbCommandText.Append("declare @ExecSql varchar(4000)\n");
            sbCommandText.Append("declare @SqlCondition varchar(1000)\n");
            sbCommandText.Append("declare @PageIndex int \n");
            sbCommandText.Append("declare @PageSize int \n");
            sbCommandText.Append("declare @IsCount tinyint \n");
            sbCommandText.Append("set @SqlCondition = ''  --初始化\n");
            #region 初始化分页参数
            sbCommandText.AppendFormat("set @PageIndex={0} \n", (dictionList.ContainsKey("pageIndex")) ? Convert.ToInt32(dictionList["pageIndex"]) : 1);
            sbCommandText.AppendFormat("set @PageSize={0} \n", (dictionList.ContainsKey("pageSize")) ? Convert.ToInt32(dictionList["pageSize"]) : 1);
            sbCommandText.AppendFormat("set @IsCount='{0}' \n", (dictionList.ContainsKey("isCount")) ? dictionList["isCount"].ToString() : "");
            #endregion

            if (dictionList.ContainsKey("UserID"))
            {
                sbCommandText.AppendFormat("set @SqlCondition=@SqlCondition+ ' and x.AddUser=''{0}'' ' \n", dictionList["UserID"].ToString());
            }

            if (dictionList.ContainsKey("WhereParams"))
            {
                sbCommandText.AppendFormat("set @SqlCondition=@SqlCondition+ ' {0}' \n", dictionList["WhereParams"].ToString());
            }


            sbCommandText.Append("if(@IsCount = '1')\n");
            sbCommandText.Append("  begin\n");
            sbCommandText.Append("      set @ExecSql = '" + string.Format(listInitSql, "", "count(*) as cx") + "' + @SqlCondition\n");
            sbCommandText.Append("  end\n");
            sbCommandText.Append("else\n");
            sbCommandText.Append("  begin\n");
            sbCommandText.Append("      set @ExecSql = '" + string.Format(listInitSql, "top ' + str(@PageSize) + '", listColumnSql) + "'\n");
            sbCommandText.Append("      if(@PageIndex = 1)\n");
            sbCommandText.Append("          begin\n");
            sbCommandText.Append("              set @ExecSql = @ExecSql + @SqlCondition + '" + listSortSql + "'\n");
            sbCommandText.Append("          end\n");
            sbCommandText.Append("      else\n");
            sbCommandText.Append("          begin\n");
            sbCommandText.Append("              set @ExecSql = @ExecSql + ' and x.ID not in (" + string.Format(listInitSql, "top ' + str((@PageIndex - 1) * @PageSize) + '", "x.ID") + "' + @SqlCondition + '" + listSortSql + ")' + @SqlCondition + '" + listSortSql + "'\n");
            sbCommandText.Append("          end\n");
            sbCommandText.Append("  end\n");
            sbCommandText.Append("exec(@ExecSql)\n");

            #endregion Sql查询脚本
            #region 执行Sql查询脚本
            return new DAL.DBExtend.DbSql().ExecuteQuery(sbCommandText.ToString());

            #endregion



            //            StringBuilder SqlCommonBuilder = new StringBuilder();
            //            string SqlCommandText = @"select {0} {1} from DatSysNews d left join DatSysNewsInfo i
            //on d.ID = i.NID where i.recevePeopleID=(select UserID From [DatUser] where LoginName='{2}') and i.IsDelete =0  {3} order by d.createDate desc ";
            //            string SqlFullCountList = "top 100";
            //            string SqlColumnsName = "d.createPeopleName,d.title,d.content,createDate=CONVERT(varchar(10), isnull(d.createDate,'1900-01-01'), 121),d.sendMode,i.ID,i.NID,i.recevePeopleID,i.readDate ";

            //            SqlCommonBuilder.AppendFormat(SqlCommandText, SqlFullCountList, SqlColumnsName, UserID, param);


            //            return new DAL.DBExtend.DbSql().ExecuteQuery(SqlCommonBuilder.ToString());
        }

        /// <summary>
        /// 我的消息反馈回复列表
        /// </summary>
        /// <param name="MsgID"></param>
        /// <returns></returns>
        public DataTable GetMSGReply_List(string MsgID)
        {
            string sql = @"select [ID] ,[MsgID] ,[UserID] ,[Content] ,ReplyTime as ReplyTime ,[IsRead], UserName=(select UName From DatUser where UserID=x.userid), ChangeType, ISNULL(DeptDescription, '') as DeptDescription From MsgReply as x where msgID='" + MsgID + "'order by ReplyTime desc";
            return new DAL.DBExtend.DbSql().ExecuteQuery(sql);
        }

        //反馈列表权限 
        public DataTable fkjyRuleList(string UserID)
        {
            string sql = @"
SELECT    SysRoleFunction.*
 FROM  SysRoleFunction INNER JOIN
  SysRole ON  SysRoleFunction.RoleID =  SysRole.RoleID INNER JOIN
  SysUserRole ON  SysRole.RoleID =  SysUserRole.RoleID INNER JOIN
  DatUser ON  SysUserRole.UserID =  DatUser.UserID
 where  DatUser.userID='{0}' 
 and functionid=(select functionid from SysFunction where Name='我的建议')
";
            sql = string.Format(sql, UserID);
            return new DAL.DBExtend.DbSql().ExecuteQuery(sql);
        }

        /// <summary>
        /// 消息分类列表
        /// </summary>
        /// <param name="dictionList"></param>
        /// <returns></returns>
        public DataSet MSGSortList(IDictionary<string, object> dictionList)
        {

            #region Sql查询脚本
            string listInitSql = @"
select *,cx=0 from Code_BaseInfo where CatNO=''10051'' and IsValid=1 and PCodeNO='''' order by OrderNO desc

select x.*, cx=ISNULL(y.tj,0) from Code_BaseInfo  x left join 
(
	select Classify,tj=count(*)
	from DatSysNews d inner join DatSysNewsInfo i on d.ID = i.NID 
	where 1=1 and i.IsDelete=0 and i.recevePeopleID=(select UserID From [DatUser] where LoginName=''" + dictionList["LoginName"].ToString() + @"'')
	and DATEDIFF(DD, d.createDate, GETDATE())=0
	group by Classify
) y on y.Classify=x.CodeNO and x.CatNO=''10051'' and x.IsValid=1 
where  x.PCodeNO<>'''' 
 ";

            //select x.*, ISNULL(y.cx, 0) as cx from Code_BaseInfo as x left join
            //(
            //select x.BaseCode,x.CodeName, COUNT(*) as cx from Code_BaseInfo as x 
            //left join (select d.title,Classify,createDate=convert(varchar(10), isnull(d.createDate, ''1900-01-01''), 121) from DatSysNews d left join DatSysNewsInfo i on d.ID = i.NID where 1=1 and i.IsDelete=0 and i.recevePeopleID=(select UserID From [DatUser] where LoginName=''" + dictionList["LoginName"].ToString() + @"'')) as y on x.CodeNO= ISNULL(y.Classify, 0)
            //where x.CatNO=''10051'' and x.IsValid=1 and x.PCodeNO<>'''' 
            //and DATEDIFF(DD, y.createDate, GETDATE())=0
            //Group by x.CodeName, x.BaseCode having COUNT(*)>1
            //) as y
            //on x.BaseCode = y.BaseCode
            //where x.CatNO=''10051'' and x.IsValid=1 and x.PCodeNO<>''''


            string listColumnSql = "x.*";

            string listSortSql = " order by x.OrderNO desc";

            StringBuilder sbCommandText = new StringBuilder();
            sbCommandText.Append("set nocount on\n");
            sbCommandText.Append("declare @ExecSql varchar(4000)\n");
            sbCommandText.Append("declare @SqlCondition varchar(1000)\n");
            sbCommandText.Append("declare @PageIndex int \n");
            sbCommandText.Append("declare @PageSize int \n");
            sbCommandText.Append("declare @IsCount tinyint \n");
            sbCommandText.Append("set @SqlCondition = ''  --初始化\n");
            #region 初始化分页参数
            sbCommandText.AppendFormat("set @PageIndex={0} \n", (dictionList.ContainsKey("pageIndex")) ? Convert.ToInt32(dictionList["pageIndex"]) : 1);
            sbCommandText.AppendFormat("set @PageSize={0} \n", (dictionList.ContainsKey("pageSize")) ? Convert.ToInt32(dictionList["pageSize"]) : 1);
            sbCommandText.AppendFormat("set @IsCount='{0}' \n", (dictionList.ContainsKey("isCount")) ? dictionList["isCount"].ToString() : "");
            #endregion

            //if (dictionList.ContainsKey("ClassName"))
            //{
            //    sbCommandText.AppendFormat("set @SqlCondition=@SqlCondition+ ' and y.ClassName=''{0}'' ' \n", dictionList["ClassName"].ToString());
            //}

            if (dictionList.ContainsKey("WhereSqlParams"))
            {
                sbCommandText.AppendFormat("set @SqlCondition=@SqlCondition+ ' {0}' \n", dictionList["WhereSqlParams"].ToString());
            }


            sbCommandText.Append("if(@IsCount = '1')\n");
            sbCommandText.Append("  begin\n");
            sbCommandText.Append("      set @ExecSql = '" + string.Format(listInitSql, "", "count(*) as cx") + "' + @SqlCondition\n");
            sbCommandText.Append("  end\n");
            sbCommandText.Append("else\n");
            sbCommandText.Append("  begin\n");
            sbCommandText.Append("      set @ExecSql = '" + string.Format(listInitSql, "top ' + str(@PageSize) + '", listColumnSql) + "'\n");
            sbCommandText.Append("      if(@PageIndex = 1)\n");
            sbCommandText.Append("          begin\n");
            sbCommandText.Append("              set @ExecSql = @ExecSql + @SqlCondition + '" + listSortSql + "'\n");
            sbCommandText.Append("          end\n");
            sbCommandText.Append("      else\n");
            sbCommandText.Append("          begin\n");
            sbCommandText.Append("              set @ExecSql = @ExecSql + ' and x.BaseCode not in (" + string.Format(listInitSql, "top ' + str((@PageIndex - 1) * @PageSize) + '", "x.BaseCode") + "' + @SqlCondition + '" + listSortSql + ")' + @SqlCondition + '" + listSortSql + "'\n");
            sbCommandText.Append("          end\n");
            sbCommandText.Append("  end\n");
            sbCommandText.Append("exec(@ExecSql)\n");

            #endregion Sql查询脚本
            #region 执行Sql查询脚本
            return new DAL.DBExtend.DbSql().Query(sbCommandText.ToString());

            #endregion
        }

        /// <summary>
        /// 消息未读总数量
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public DataTable GetMSGListCount(string UserID)
        {
            string sql = @"
            select count(*) as ct from DatSysNews d left join DatSysNewsInfo i on d.ID = i.NID
where i.recevePeopleID=(select UserID From [DatUser] where LoginName='{0}') and d.ISDelete=0  and i.readDate is null";
            return new DAL.DBExtend.DbSql().ExecuteQuery(string.Format(sql, UserID));
        }


        /// <summary>
        /// 根据userid获取手机号码
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public string GetUserPhone(string UserID)
        {
            string sql = @"select PhoneNo from hrEmployee where ID=(select empid from DatUser where UserID='{0}')";
            return new DAL.DBExtend.DbSql().GetSingle(string.Format(sql, UserID)).ToString();
        }


        /// <summary>
        /// 获取员工风采
        /// </summary>
        /// <param name="UserID">string UserID, string param</param>
        /// <returns></returns>
        public DataTable DatMessageQueue(IDictionary<string, object> dictionList)
        {
            #region Sql查询脚本

            string listInitSql = "select {0} {1} From [tmInformation] where ClassType=1 ";

            string listColumnSql = "ID as formid, Title as formname, REPLACE(CONVERT(varchar(max), Contents),''\\\"'','''''''') as htmlstring, isnull(FileNamePath,'''') as FileNamePath";

            string listSortSql = " order by AddTime desc";

            StringBuilder sbCommandText = new StringBuilder();
            sbCommandText.Append("set nocount on\n");
            sbCommandText.Append("declare @ExecSql varchar(4000)\n");
            sbCommandText.Append("declare @SqlCondition varchar(1000)\n");
            sbCommandText.Append("declare @PageIndex int \n");
            sbCommandText.Append("declare @PageSize int \n");
            sbCommandText.Append("declare @IsCount tinyint \n");
            sbCommandText.Append("set @SqlCondition = ''  --初始化\n");
            #region 初始化分页参数
            sbCommandText.AppendFormat("set @PageIndex={0} \n", (dictionList.ContainsKey("pageIndex")) ? Convert.ToInt32(dictionList["pageIndex"]) : 1);
            sbCommandText.AppendFormat("set @PageSize={0} \n", (dictionList.ContainsKey("pageSize")) ? Convert.ToInt32(dictionList["pageSize"]) : 1);
            sbCommandText.AppendFormat("set @IsCount='{0}' \n", (dictionList.ContainsKey("isCount")) ? dictionList["isCount"].ToString() : "");
            #endregion

            sbCommandText.Append("if(@IsCount = '1')\n");
            sbCommandText.Append("  begin\n");
            sbCommandText.Append("      set @ExecSql = '" + string.Format(listInitSql, "", "count(*) as cx") + "' + @SqlCondition\n");
            sbCommandText.Append("  end\n");
            sbCommandText.Append("else\n");
            sbCommandText.Append("  begin\n");
            sbCommandText.Append("      set @ExecSql = '" + string.Format(listInitSql, "top ' + str(@PageSize) + '", listColumnSql) + "'\n");
            sbCommandText.Append("      if(@PageIndex = 1)\n");
            sbCommandText.Append("          begin\n");
            sbCommandText.Append("              set @ExecSql = @ExecSql + @SqlCondition + '" + listSortSql + "'\n");
            sbCommandText.Append("          end\n");
            sbCommandText.Append("      else\n");
            sbCommandText.Append("          begin\n");
            sbCommandText.Append("              set @ExecSql = @ExecSql + ' and ID not in (" + string.Format(listInitSql, "top ' + str((@PageIndex - 1) * @PageSize) + '", "ID") + "' + @SqlCondition + '" + listSortSql + ")' + @SqlCondition + '" + listSortSql + "'\n");
            sbCommandText.Append("          end\n");
            sbCommandText.Append("  end\n");
            sbCommandText.Append("exec(@ExecSql)\n");

            #endregion Sql查询脚本
            #region 执行Sql查询脚本
            return new DAL.DBExtend.DbSql().ExecuteQuery(sbCommandText.ToString());

            #endregion
        }

        /// <summary>
        /// 培训资料
        /// </summary>
        /// <param name="dictionList"></param>
        /// <returns></returns>
        public DataTable PxzlDataList(IDictionary<string, object> dictionList)
        {
            #region Sql 查询脚本
            string listInitSql = "select {0} {1} from [tmInformation] where ClassType=0 {2} {3}";
            string listColumnSql = "ID as formid, Title as formname, REPLACE(CONVERT(varchar(max), Contents),'\\\"','''') as htmlstring, isnull(FileNamePath,'') as FileNamePath";
            string listSortSql = " order by AddTime desc";

            StringBuilder sbCommandText = new StringBuilder();
            sbCommandText.AppendFormat(listInitSql, " ", listColumnSql,
                dictionList.ContainsKey("WhereParamBase") ? dictionList["WhereParamBase"].ToString() : ""
                , listSortSql);
            #endregion
            #region 执行Sql查询脚本
            return new DAL.DBExtend.DbSql().ExecuteQuery(sbCommandText.ToString());

            #endregion
        }




        /// <summary>
        /// 设置消息已读
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public int SetMSGYD(string UserID, string ID)
        {
            StringBuilder SqlCommonBuilder = new StringBuilder();
            string SqlCommandText = @"update y set y.readDate=GETDATE() From DatSysNews as x left join DatSysNewsInfo as y 
on x.id=y.NID where y.recevePeopleID=(select UserID From [DatUser] where LoginName='{0}') and y.readDate is null and x.ID='{1}'";

            SqlCommonBuilder.AppendFormat(SqlCommandText, UserID, ID);

            return new DAL.DBExtend.DbSql().ExecuteScalar(SqlCommonBuilder.ToString());

        }
        /// <summary>
        /// 上一条/下一条
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="param"></param>
        /// <returns></returns>

        public DataTable GetMoveNextData(IDictionary<string, object> dictionList)
        {
            StringBuilder SqlCommonBuilder = new StringBuilder();
            SqlCommonBuilder.Append("declare @cd datetime ");
            SqlCommonBuilder.Append("select @cd=d.createDate from DatSysNews d left join DatSysNewsInfo i on d.ID = i.NID where 1=1 ");
            if (dictionList.ContainsKey("recordID"))
            {
                SqlCommonBuilder.AppendFormat("and d.ID='{0}'", dictionList["recordID"].ToString());
            }



            SqlCommonBuilder.AppendFormat("select top 1 d.ID,d.title From DatSysNews d left join DatSysNewsInfo i on d.ID=i.NID where i.recevePeopleID=(select UserID From [DatUser] where LoginName='{0}')  and d.IsDelete =0  {1}",
                dictionList["LoginName"].ToString(), dictionList["WhereParams"].ToString());
            return new DAL.DBExtend.DbSql().ExecuteQuery(SqlCommonBuilder.ToString());

        }


        /// <summary>
        /// 全部设为已读
        /// </summary>
        /// <param name="strWorkFlowInstanceId"></param>
        /// <returns></returns>
        public int SetAllYD(string userid)
        {
            StringBuilder SqlCommonBuilder = new StringBuilder();
            string SqlCommandText = @"update i set i.readDate=GETDATE() from DatSysNews as d left join DatSysNewsInfo as i on d.ID = i.NID where 1=1 and i.IsDelete=0
 and  i.recevePeopleID=(select UserID From [DatUser] where LoginName='{0}') and i.readDate is null";

            SqlCommonBuilder.AppendFormat(SqlCommandText, userid);
            return new DAL.DBExtend.DbSql().ExecuteScalar(SqlCommonBuilder.ToString());
        }


        /// <summary>
        /// 待办
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public DataTable GetWFItem(string UserID)
        {
            string sql = "SELECT w.WorkFlowId,w.Caption,w.FlowCaption,(select COUNT(*) from  WorkFlowInstanceClassView where WorkFlowId=w.WorkFlowID and UserId=W.UserId),w.UserId from WorkFlowInstanceClassView as W  where  userID='{0}' group by WorkFlowId,UserId,Caption,w.FlowCaption";
            return new DAL.DBExtend.DbSql().ExecuteQuery(string.Format(sql, UserID));
        }

        /// <summary>
        /// 待办事项作废
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public int ModifyWFIStatus(string strWorkFlowInstanceId)
        {
            StringBuilder SqlCommonBuilder = new StringBuilder();
            string SqlCommandText = @"update x set Status=4 From HF_WorkFlowInstance as x where WorkFlowInstanceId='{0}'";

            SqlCommonBuilder.AppendFormat(SqlCommandText, strWorkFlowInstanceId);
            return new DAL.DBExtend.DbSql().ExecuteScalar(SqlCommonBuilder.ToString());
        }

        /// <summary>
        /// 通讯录部门表
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public DataTable GetTXLBMItems(string param)
        {
            StringBuilder SqlCommonBuilder = new StringBuilder();
            string SqlCommandText = "select {0} {1} From V_hrEmployeeContactsDepartment where 1=1 and bm<>''";
            string SqlFullCountList = "";
            string SqlColumnsName = "DepartmentCode, bm";
            string SqlOrderBy = " order by DepartmentCode asc";

            SqlCommonBuilder.AppendFormat(SqlCommandText, SqlFullCountList, SqlColumnsName, SqlOrderBy);
            return new DAL.DBExtend.DbSql().ExecuteQuery(SqlCommonBuilder.ToString());
        }


        /// <summary>
        /// 通讯录人员表
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public DataTable GetTXLItems(IDictionary<string, object> sqlParamList)
        {
            StringBuilder SqlCommonBuilder = new StringBuilder();
            string SqlCommandText = "select {0} {1} from V_employee_Contacts where 1=1 {2} {3}";
            string SqlFullCountList = "";
            string SqlColumnsName = "ID, isnull(CName,'') as CName, isnull(DepartmentName,'') as DepartmentName, isnull(PostNames,'') as PostNames,isnull(Name,'') as Name, isnull(companyCornet,'') as companyCornet, isnull(PhoneNos,'') as PhoneNos, isnull(companyCornets,'') as companyCornets, isnull(ShortDeptName, '') as ShortDeptName";
            string SqlOrderBy = "order by  dataType Desc, DepartmentCode asc, SequenceNo desc, CName Asc";

            SqlCommonBuilder.AppendFormat(SqlCommandText, SqlFullCountList, SqlColumnsName,
                (sqlParamList.ContainsKey("sqlWhereParam")) ? sqlParamList["sqlWhereParam"].ToString() : "",
                SqlOrderBy);
            return new DAL.DBExtend.DbSql().ExecuteQuery(SqlCommonBuilder.ToString());
        }

        /// <summary>
        /// 部门
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public DataTable GetTXLItems(string s)
        {
            StringBuilder SqlCommonBuilder = new StringBuilder();
            string SqlCommandText = @"select dbo.fn_GetDeptName(z.DepartmentID, '2') AS ShortDeptName
From (select x.* From hrEmployee as x inner join DatUser as y on x.ID=y.EmpID where y.UserID='{0}') as z";
            SqlCommonBuilder.AppendFormat(SqlCommandText, s);
            return new DAL.DBExtend.DbSql().ExecuteQuery(SqlCommonBuilder.ToString());
        }

        /// <summary>
        /// 我的审批列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable WorkFlowApproveViewItems(string userid, string tableName, string whereParam, string orderParam, string searchName, string userName)
        {
            StringBuilder SqlCommonBuilder = new StringBuilder();
            string SqlCommandText = "select {0} {1} from {2} where 1=1 {3} and ( UserId='{4}') and OperContentText<> '" + userName + "' Order by {5}";
            string SqlFullCountList = "";
            string SqlColumnsName = "OperatorInstanceId, WorkTaskInstanceId, WorkFlowId, FlowCaption, OperatedDes, Caption, FlowCaptions, [Status], OperContentText";

            SqlCommonBuilder.AppendFormat(SqlCommandText, SqlFullCountList, SqlColumnsName, tableName,
                (whereParam) + ((!string.IsNullOrEmpty(searchName)) ? " and FlowCaptions like '%" + searchName + "%'" : ""), userid, orderParam);
            return new DAL.DBExtend.DbSql().ExecuteQuery(SqlCommonBuilder.ToString());
        }

        /// <summary>
        /// 撤销申请(退回流程)
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public DataTable Exec_OperatorBack(string WorkFlowInstanceId, string UserID)
        {
            StringBuilder SqlCommonBuilder = new StringBuilder();
            SqlCommonBuilder.Append("declare @WorkFlowInstanceId varchar(50), @OperatorInstanceId varchar(50), @UserID varchar(50) \n");
            SqlCommonBuilder.Append("declare @operatorInstanceCX tinyint, @operatorUser varchar(20) \n");
            SqlCommonBuilder.Append("set @WorkFlowInstanceId='" + WorkFlowInstanceId + "' \n");
            SqlCommonBuilder.Append("set @UserID='" + UserID + "' \n");
            SqlCommonBuilder.Append("begin tran tranRevocation \n");
            SqlCommonBuilder.Append("   select @operatorInstanceCX=COUNT(*) From [HF_operatorinstance] where WorkFlowInstanceId=@WorkFlowInstanceId and OperType=1 \n");
            SqlCommonBuilder.Append("	select top 1 @operatorUser=ISNULL(userid, ''), @OperatorInstanceId=OperatorInstanceId From [HF_operatorinstance] where WorkFlowInstanceId=@WorkFlowInstanceId and OperType=1 \n");
            SqlCommonBuilder.Append("	if(@operatorInstanceCX is null or @operatorInstanceCX<>1 or @operatorUser<>'') goto Error \n");
            SqlCommonBuilder.Append("	if(@OperatorInstanceId is null or @UserID is null) goto Error \n");
            SqlCommonBuilder.Append("	exec HF_WorkTaskClaimPro @OperatorInstanceId=@OperatorInstanceId,@UserId=@UserID \n");
            SqlCommonBuilder.Append("	exec HF_WorkTaskBackProApplicant @OperatorInstanceId=@OperatorInstanceId,@UserId=@UserID \n");
            SqlCommonBuilder.Append("	if @@ERROR <>0 goto Error \n");
            SqlCommonBuilder.Append("	select '1' as bc \n");
            SqlCommonBuilder.Append("commit tran tranRevocation \n");
            SqlCommonBuilder.Append("return \n");
            SqlCommonBuilder.Append("Error: \n");
            SqlCommonBuilder.Append("	begin \n");
            SqlCommonBuilder.Append("		select '0' as bc \n");
            SqlCommonBuilder.Append("		rollback tran tranRevocation \n");
            SqlCommonBuilder.Append("	end \n");

            return new DAL.DBExtend.DbSql().ExecuteQuery(SqlCommonBuilder.ToString());
        }


        /// <summary>
        /// 获取可以发起的流程
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public DataTable GetWFItemList(string UserID)
        {
            string sql = "SELECT * from WorkFlowInstanceClassView as W ,HF_WorkFlowInstance wfi where wfi.WorkFlowInstanceId=w.WorkFlowInstanceId and userID='{0}' order by w.WorkFlowNo desc";
            return new DAL.DBExtend.DbSql().ExecuteQuery(string.Format(sql, UserID));
        }

        /// <summary>
        /// 取得待确认考勤记录数
        /// </summary>
        /// <param name="employeeID"></param>
        public int GetWorkResult(string employeeID)
        {
            string sql = string.Format("select COUNT(*) as totalCount from wkWorkResult where IsValidate='N' and WkStatus not in('正常出勤','非工作日','项目中','加班')  and EmployeeID ='{0}'", employeeID);
            DbSql dbsql = new DbSql();
            return dbsql.ExecuteScalar(sql);
        }

        /// <summary>
        /// 根据部门OR姓名获取人员
        /// </summary>
        /// <param name="deptid"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataTable GetDatUser(string deptid, string name)
        {
            string sql = "";
            if (name == string.Empty || name == "")
            {
                sql = string.Format("select Top 50 * from V_TrSendQuestionnaireUser where cast(DeptID as varchar(50))='{0}'", deptid);
            }
            else if (deptid == string.Empty || deptid == "")
            {
                sql = string.Format("select Top 50 * from V_TrSendQuestionnaireUser where  Name like '%{0}%'", name);
            }
            else if (deptid == "sczx")
            {
                sql = name;
            }
            else
            {
                sql = string.Format("select Top 50 * from V_TrSendQuestionnaireUser where cast(DeptID as varchar(50))='{0}' or Name like '%{1}%'", deptid, name);
            }
            DbSql dbsql = new DbSql();
            return dbsql.ExecuteQuery(sql);
        }

        /// <summary>
        /// 检测登陆认证入口点
        /// </summary>
        /// <param name="dictionList"></param>
        /// <returns></returns>
        public ZX.EHR.Library.DatUser CheckedBinder(IDictionary<string, object> dictionList)
        {
            ZX.EHR.Library.DatUser user = null;

            DataTable dataTable = new DataTable();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(@"select top 1 x.LoginName, x.Password From datuser as x left join DatUser_Mobile as y 
on x.UserID= y.UserId where 1=1 and y.isDelete=0 and y.isEnable=1 ");

            if (dictionList.ContainsKey("mid"))
            {
                commandText.AppendFormat(" and y.mobileID='{0}'", dictionList["mid"].ToString());
            }

            dataTable = new DAL.DBExtend.DbSql().ExecuteQuery(commandText.ToString());
            //throw new Exception("error:" + dataTable.Rows[0][0].ToString() + "/" + dataTable.Rows[0]["LoginName"].ToString());
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                user = new EHR.Library.DatUser();
                user.LoginName = dataTable.Rows[0]["LoginName"].ToString();
                user.Password = dataTable.Rows[0]["Password"].ToString();
            }

            return user;
        }

        /// <summary>
        /// 登陆认证唯一性认证
        /// </summary>
        /// <param name="dictionList"></param>
        /// <returns></returns>
        public ZX.EHR.Library.DatUser IsInBaseOfMID(IDictionary<string, object> dictionList)
        {
            ZX.EHR.Library.DatUser user = null;

            DataTable dataTable = new DataTable();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(@"select top 1 x.LoginName, x.Password From datuser as x left join DatUser_Mobile as y 
on x.UserID= y.UserId where 1=1 and y.isDelete=0 and y.isEnable=0 ");

            if (dictionList.ContainsKey("mid"))
            {
                commandText.AppendFormat(" and y.mobileID='{0}'", dictionList["mid"].ToString());
            }

            dataTable = new DAL.DBExtend.DbSql().ExecuteQuery(commandText.ToString());
            //throw new Exception("error:" + dataTable.Rows[0][0].ToString() + "/" + dataTable.Rows[0]["LoginName"].ToString());
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                user = new EHR.Library.DatUser();
                user.LoginName = dataTable.Rows[0]["LoginName"].ToString();
                user.Password = dataTable.Rows[0]["Password"].ToString();
            }

            return user;
        }

        public int ExNonQueryBy_MobileBase(IDictionary<string, object> dictionList)
        {
            int result = 0;
            StringBuilder commandText = new StringBuilder();
            commandText.Append("Begin tran tranProcess \n");
            commandText.Append("declare @cx tinyint, @mid varchar(50) \n");
            commandText.AppendFormat(" set @mid='{0}' \n", ((dictionList.ContainsKey("mobileID")) ? dictionList["mobileID"].ToString() : ""));
            commandText.Append("select @cx=count(*) From DatUser_Mobile where mobileID=@mid \n");
            commandText.Append("if(@cx>0) goto Error \n");
            commandText.Append("if(@mid='') goto Error \n");
            commandText.AppendFormat(@"insert into DatUser_Mobile (ID, UserId, app_key, master_secret, mobileID, mobiletype, AddDate, IsDelete, IsEnable, [version]) 
values (NEWID(), '{0}', '{1}', '{2}', '{3}', '{4}', GETDATE(), {5}, {6}, {7} )",
            (dictionList.ContainsKey("UserId")) ? dictionList["UserId"].ToString() : "",
            (dictionList.ContainsKey("app_key")) ? dictionList["app_key"].ToString() : "",
            (dictionList.ContainsKey("master_secret")) ? dictionList["master_secret"].ToString() : "",
            (dictionList.ContainsKey("mobileID")) ? dictionList["mobileID"].ToString() : "",
            (dictionList.ContainsKey("mobiletype")) ? dictionList["mobiletype"].ToString() : "",
            (dictionList.ContainsKey("IsDelete")) ? Convert.ToInt32(dictionList["IsDelete"]) : 0,
            (dictionList.ContainsKey("IsEnable")) ? Convert.ToInt32(dictionList["IsEnable"]) : 0,
            (dictionList.ContainsKey("version")) ? Convert.ToInt32(dictionList["version"]) : 0
            );
            commandText.Append("commit tran tranProcess \n");
            commandText.Append("return \n");
            commandText.Append(" Error: \n");
            commandText.Append(" begin \n");
            commandText.Append("    print '已存在相同的记录! 请联系ADI'  \n");
            commandText.Append("    rollback tran tranProcess \n");
            commandText.Append(" end \n");



            DbSql dbsql = new DbSql();
            result = dbsql.ExecuteScalar(commandText.ToString());
            return result;

        }
    }
}